# -*- coding: utf-8 -*-

import sqlite3
import pymysql
from system.Log import Logger

log = Logger("sql")


class LiteDB:
    """
        操作sqlite3数据库的类
    """

    def __init__(self, db_path: str):
        self.__db_path = db_path
        self.__connect = sqlite3.connect(self.__db_path)
        self.__connect.row_factory = self.dict_factory  # 参考官网设置输出格式; https://docs.python.org/2/library/sqlite3.html#sqlite3.Connection.row_factory
        self.cursor = self.__connect.cursor()

    def execute(self, dql: str):
        """
        执行数据库语句
        :param dql: 数据库语句
        :return:
        """
        log.logger.info("执行数据库语句：" + dql)

        self.cursor.execute(dql)

    def commit(self):
        """
        提交数据库的事务
        :return:
        """
        self.__connect.commit()

    def __del__(self):
        """
        内存回收前关闭数据库的连接
        :return:
        """
        self.cursor.close()
        del self

    def into(self, table_name: str, keys: tuple, values: tuple):
        """
        执行sqlite3数据库插入数据
        :param table_name: 表格名称
        :param keys: 插入数据的字段名
        :param values: 出入数据的值
        :return:
        """

        dql = f"""insert into {table_name} {keys} values {values} """
        self.execute(dql)
        self.commit()

    def query(self, dql):
        """
        执行sqlite3数据库查询语句
        :param dql: 查询语句
        :return:
        """
        self.execute(dql)
        return self.cursor.fetchall()

    @staticmethod
    def dict_factory(cursor, row):
        """
        这是参照sqlite3 官网对于sqlite3查询返回的数据进行格式转化为字典的处理方式
        https://docs.python.org/2/library/sqlite3.html#sqlite3.Connection.row_factory
        :return:
        """
        d = {}
        for idx, col in enumerate(cursor.description):
            d[col[0]] = row[idx]
        return d


class MysqlDB:
    """
        操作mysql数据库的类
    """

    def __init__(self, host, user, password, database, port=3306, charset='utf8'):
        self._host = host
        self._user = user
        self._password = password
        self._database = database
        self._port = port
        self._charset = charset
        self._conn = None
        self._cursor = None
        try:
            self._conn = pymysql.connect(
                host=self._host,
                user=self._user,
                password=self._password,
                database=self._database,
                charset=self._charset,
                port=self._port,
                autocommit=True
            )
        except Exception as err:
            format_err = f"ERROR - {self._host} session init failed: {err}" + "\n"
            raise Exception(format_err)

    # 调用with方法的入口
    def __enter__(self):
        return self

    # # 调用with方法结束时启动
    def __exit__(self, exc_type, exc_val, exc_tb):
        self._conn.close()

    # 查询函数
    def query(self, dql):
        """
        :param dql: 查询语句
        :return: result-查询结果;
        """

        cursor = self._conn.cursor(pymysql.cursors.DictCursor)  # 设置返回数据为字典返回
        try:
            log.logger.info("执行数据库语句：" + dql)
            cursor.execute(dql)
            result = cursor.fetchall()
            cursor.close()
            return result
        except Exception as err:
            format_err = f"ERROR - {self._host} query failed: {err} - {dql}" + "\n"
            raise Exception(format_err)
        finally:
            cursor.close()

    # 单条dml语句
    def change(self, dql):
        """
        :param dql: 数据库语句
        :return : rows-改动的记录条数
        """
        cursor = self._conn.cursor()

        try:
            log.logger.info("执行数据库语句：" + dql)

            rows = cursor.execute(dql)
            return rows
        except Exception as err:
            format_err = f"ERROR - {self._host} change failed: {err} - {dql}" + "\n"
            raise Exception(format_err)
        finally:
            cursor.close()

    # 多条dml语句
    def change_many(self, dql, value_list):
        """
        :param dql: 数据库语句
        :param value_list: 参数值列表
        :return:rows-改动的记录条数
        """
        cursor = self._conn.cursor()
        try:
            log.logger.info("执行数据库语句：" + dql)

            rows = cursor.executemany(dql, value_list)
            return rows
        except Exception as err:
            format_err = f"ERROR - {self._host} change_many failed: {err} - {dql}" + "\n"
            raise Exception(format_err)
        finally:
            cursor.close()


if __name__ == '__main__':
    LiteDB("../identifier.sqlite").into("dsadsada", ("keys",), ("values",))
    # sql = LiteDB("../identifier.sqlite")
    # sql.query("select * from test_cases")
    # s = sql.query("select count(*) as count from test_cases;")
    # print(s)
    # print(sql.table_names("test_cases"))
